USE [TestingSystem]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_course_get_all')
   DROP PROCEDURE [dbo].[sp_course_get_all]
GO

CREATE PROCEDURE [dbo].[sp_course_get_all]   
AS
BEGIN

	SELECT [COURSE_ID], [COURSE_NAME]
	FROM [dbo].[COURSE]
	ORDER BY [COURSE_NAME]

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_user_get_all')
   DROP PROCEDURE [dbo].[sp_user_get_all]
GO

CREATE PROCEDURE [dbo].[sp_user_get_all]   
AS
BEGIN

	SELECT [USER_ID], [USER_FIRST_NAME], [USER_LAST_NAME]
	FROM [dbo].[USER]
	ORDER BY [USER_FIRST_NAME], [USER_LAST_NAME]

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_user_test_course_load')
   DROP PROCEDURE [dbo].[sp_user_test_course_load]
GO

CREATE PROCEDURE [dbo].[sp_user_test_course_load]
     @COURSE_ID char(10)
    ,@USER_ID char(10)
    ,@TEST_START_TIME datetime
AS
BEGIN

	DECLARE @isWhere int;
	SET @isWhere = 0;
	DECLARE @query varchar(4000);
	SET @query = '
		SELECT T.TEST_ID, C.COURSE_ID, C.COURSE_NAME, 
			U.USER_ID, U.USER_FIRST_NAME, U.USER_LAST_NAME, T.TEST_DESC, T.TEST_CREATION_DATE, T.TEST_START_TIME, T.TEST_DURATION
		FROM [dbo].[TEST] T 
			INNER JOIN [dbo].[USER] U ON T.USER_ID = U.USER_ID
			INNER JOIN [dbo].[COURSE] C ON T.COURSE_ID = C.COURSE_ID';
	
	IF @COURSE_ID IS NOT NULL AND LEN(LTRIM(RTRIM(@COURSE_ID))) > 0
	BEGIN
		SET @query = @query + '	WHERE LTRIM(RTRIM(C.COURSE_ID)) = ''' + LTRIM(RTRIM(@COURSE_ID)) + '''';
		SET @isWhere = 1;
	END

	IF @USER_ID IS NOT NULL AND LEN(LTRIM(RTRIM(@USER_ID))) > 0
	BEGIN
		IF @isWhere = 0
		BEGIN
			SET @query = @query + '	WHERE LTRIM(RTRIM(U.USER_ID)) = ''' + LTRIM(RTRIM(@USER_ID)) + '''';
			SET @isWhere = 1;
		END
		ELSE
			SET @query = @query + '	AND LTRIM(RTRIM(U.USER_ID)) = ''' + LTRIM(RTRIM(@USER_ID)) + '''';
	END

	IF @TEST_START_TIME IS NOT NULL
	BEGIN
		IF @isWhere = 0
		BEGIN
			SET @query = @query + '	WHERE CAST(T.TEST_START_TIME AS DATE) = ''' + CAST(CAST(@TEST_START_TIME AS DATE) AS varchar(20)) + '''';
			SET @isWhere = 1;
		END
		ELSE
			SET @query = @query + '	AND CAST(T.TEST_START_TIME AS DATE) = ''' + CAST(CAST(@TEST_START_TIME AS DATE) AS varchar(20)) + '''';
	END

	EXEC (@query);

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_get')
   DROP PROCEDURE [dbo].[sp_test_get]
GO

CREATE PROCEDURE [dbo].[sp_test_get]
     @TEST_ID numeric(18, 0)
    
AS
BEGIN

	SELECT *
	FROM [dbo].[TEST]
	WHERE TEST_ID = @TEST_ID;

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_load')
   DROP PROCEDURE [dbo].[sp_test_section_load]
GO

CREATE PROCEDURE [dbo].[sp_test_section_load]
	@TEST_ID numeric(18, 0)
	,@COURSE_ID char(10)
	,@USER_ID char(10)
    
AS
BEGIN

	DECLARE @isWhere int;
	SET @isWhere = 0;
	DECLARE @query varchar(4000);
	SET @query = '
		SELECT TMP.TEST_SECTION_ID, S.TEST_SECTION_DESC, TMP.NUM_QUESTION
		FROM 
			(SELECT S.TEST_ID, S.TEST_SECTION_ID, COUNT(I.TEST_SECTION_ITEM_ID) AS NUM_QUESTION
			FROM [dbo].[TEST_SECTION] S INNER JOIN [dbo].[TEST_SECTION_ITEM] I ON I.TEST_SECTION_ID = S.TEST_SECTION_ID
			GROUP BY S.TEST_ID, S.TEST_SECTION_ID) AS TMP
			INNER JOIN [dbo].[TEST] T ON TMP.TEST_ID = T.TEST_ID
			INNER JOIN [dbo].[TEST_SECTION] S ON TMP.TEST_SECTION_ID = S.TEST_SECTION_ID';
	
	IF @TEST_ID IS NOT NULL
	BEGIN
		SET @query = @query + '	WHERE T.TEST_ID = ' + @TEST_ID;
		SET @isWhere = 1;
	END

	IF @COURSE_ID IS NOT NULL AND LEN(LTRIM(RTRIM(@COURSE_ID))) > 0
	BEGIN
		IF @isWhere = 0
		BEGIN
			SET @query = @query + '	WHERE LTRIM(RTRIM(T.COURSE_ID)) = ''' + LTRIM(RTRIM(@COURSE_ID)) + '''';
			SET @isWhere = 1;
		END
		ELSE
		BEGIN
			SET @query = @query + '	AND LTRIM(RTRIM(T.COURSE_ID)) = ''' + LTRIM(RTRIM(@COURSE_ID)) + '''';
		END
	END

	IF @USER_ID IS NOT NULL AND LEN(LTRIM(RTRIM(@USER_ID))) > 0
	BEGIN
		IF @isWhere = 0
			SET @query = @query + '	WHERE LTRIM(RTRIM(T.USER_ID)) = ''' + LTRIM(RTRIM(@USER_ID)) + '''';
		ELSE
			SET @query = @query + '	AND LTRIM(RTRIM(T.USER_ID)) = ''' + LTRIM(RTRIM(@USER_ID)) + '''';
	END

	EXEC (@query);

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_get')
   DROP PROCEDURE [dbo].[sp_test_section_get]
GO

CREATE PROCEDURE [dbo].[sp_test_section_get]
	@TEST_ID numeric(18, 0)
    
AS
BEGIN

	SELECT TMP.TEST_SECTION_ID, S.TEST_SECTION_DESC, TMP.NUM_QUESTION
	FROM 
		(SELECT S.TEST_ID, S.TEST_SECTION_ID, COUNT(I.TEST_SECTION_ITEM_ID) AS NUM_QUESTION
		FROM [dbo].[TEST_SECTION] S INNER JOIN [dbo].[TEST_SECTION_ITEM] I ON I.TEST_SECTION_ID = S.TEST_SECTION_ID
		GROUP BY S.TEST_ID, S.TEST_SECTION_ID) AS TMP
		INNER JOIN [dbo].[TEST] T ON TMP.TEST_ID = T.TEST_ID
		INNER JOIN [dbo].[TEST_SECTION] S ON TMP.TEST_SECTION_ID = S.TEST_SECTION_ID
	WHERE T.TEST_ID = @TEST_ID;

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_load_filter')
   DROP PROCEDURE [dbo].[sp_question_load_filter]
GO

CREATE PROCEDURE [dbo].[sp_question_load_filter]
	@USER_ID char(10)
    ,@QUESTION_LEVEL char(1)
	,@QUESTION_TYPE char(15)
AS
BEGIN

	DECLARE @isWhere int;
	SET @isWhere = 0;
	DECLARE @query varchar(4000);
	SET @query = '
		SELECT U.USER_ID, Q.QUESTION_ID, Q.QUESTION_DESCRIPTION, Q.QUESTION_LEVEL, Q.QUESTION_TYPE
		FROM [dbo].[QUESTION] Q INNER JOIN [dbo].[USER] U ON Q.USER_ID = U.USER_ID';
	
	IF @USER_ID IS NOT NULL AND LEN(LTRIM(RTRIM(@USER_ID))) > 0
	BEGIN
		SET @query = @query + '	WHERE LTRIM(RTRIM(U.USER_ID)) = ''' + LTRIM(RTRIM(@USER_ID)) + '''';
		SET @isWhere = 1;
	END

	IF @QUESTION_LEVEL IS NOT NULL AND LEN(LTRIM(RTRIM(@QUESTION_LEVEL))) > 0
	BEGIN
		IF @isWhere = 0
		BEGIN
			SET @query = @query + '	WHERE LTRIM(RTRIM(Q.QUESTION_LEVEL)) = ''' + LTRIM(RTRIM(@QUESTION_LEVEL)) + '''';
			SET @isWhere = 1;
		END
		ELSE
			SET @query = @query + '	AND LTRIM(RTRIM(Q.QUESTION_LEVEL)) = ''' + LTRIM(RTRIM(@QUESTION_LEVEL)) + '''';
	END

	IF @QUESTION_TYPE IS NOT NULL AND LEN(LTRIM(RTRIM(@QUESTION_TYPE))) > 0
	BEGIN
		IF @isWhere = 0
		BEGIN
			SET @query = @query + '	WHERE LTRIM(RTRIM(Q.QUESTION_TYPE)) = ''' + LTRIM(RTRIM(@QUESTION_TYPE)) + '''';
		END
		ELSE
			SET @query = @query + '	AND LTRIM(RTRIM(Q.QUESTION_TYPE)) = ''' + LTRIM(RTRIM(@QUESTION_TYPE)) + '''';
	END

	EXEC (@query);

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_insert_return')
   DROP PROCEDURE [dbo].[sp_test_insert_return]
GO

CREATE PROCEDURE [dbo].[sp_test_insert_return]
     @COURSE_ID char(10)
    ,@USER_ID char(10)
    ,@TEST_DESC ntext
    ,@TEST_CREATION_DATE datetime
    ,@TEST_START_TIME datetime
    ,@TEST_DURATION int

AS
BEGIN

    INSERT INTO [dbo].[TEST]
    ( 
         [COURSE_ID]
        ,[USER_ID]
        ,[TEST_DESC]
        ,[TEST_CREATION_DATE]
        ,[TEST_START_TIME]
        ,[TEST_DURATION]
    )
    VALUES
    (
         @COURSE_ID
        ,@USER_ID
        ,@TEST_DESC
        ,@TEST_CREATION_DATE
        ,@TEST_START_TIME
        ,@TEST_DURATION
    );

	RETURN @@IDENTITY;

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_insert_return')
   DROP PROCEDURE [dbo].[sp_test_section_insert_return]
GO

CREATE PROCEDURE [dbo].[sp_test_section_insert_return]
     @TEST_ID numeric
    ,@TEST_SECTION_DESC ntext

AS
BEGIN

    INSERT INTO [dbo].[TEST_SECTION]
    ( 
         [TEST_ID]
        ,[TEST_SECTION_DESC]
    )
    VALUES
    (
         @TEST_ID
        ,@TEST_SECTION_DESC
    );

	RETURN @@IDENTITY;
END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_item_insert_all')
   DROP PROCEDURE [dbo].[sp_test_section_item_insert_all]
GO

CREATE PROCEDURE [dbo].[sp_test_section_item_insert_all]
     @TEST_SECTION_ID numeric
    ,@QUESTION_IDs varchar(MAX)
    
AS
BEGIN
	
	DECLARE @query varchar(MAX);
    SET @query = 'INSERT INTO [dbo].[TEST_SECTION_ITEM]
    ( 
         [TEST_SECTION_ID]
        ,[QUESTION_ID]
    )
    SELECT ' + CAST(@TEST_SECTION_ID AS varchar(18)) + ', [QUESTION_ID]
	FROM [QUESTION]
	WHERE [QUESTION_ID] IN (' + @QUESTION_IDs + ')';

	EXEC (@query);

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_get_questions')
   DROP PROCEDURE [dbo].[sp_test_section_get_questions]
GO

CREATE PROCEDURE [dbo].[sp_test_section_get_questions]
	@TEST_SECTION_ID numeric
    
AS
BEGIN

	SELECT Q.QUESTION_ID, Q.USER_ID, Q.QUESTION_DESCRIPTION, Q.QUESTION_LEVEL, Q.QUESTION_TYPE
	FROM [dbo].[TEST_SECTION] S INNER JOIN [dbo].[TEST_SECTION_ITEM] I ON I.TEST_SECTION_ID = S.TEST_SECTION_ID
		INNER JOIN [dbo].[QUESTION] Q ON Q.QUESTION_ID = I.QUESTION_ID
	WHERE S.TEST_SECTION_ID = @TEST_SECTION_ID;

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_get_info')
   DROP PROCEDURE [dbo].[sp_test_section_get_info]
GO

CREATE PROCEDURE [dbo].[sp_test_section_get_info]
	@TEST_SECTION_ID numeric
    
AS
BEGIN

	SELECT TEST_ID, TEST_SECTION_ID, TEST_SECTION_DESC
	FROM [dbo].[TEST_SECTION]
	WHERE TEST_SECTION_ID = @TEST_SECTION_ID;

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_item_update_all')
   DROP PROCEDURE [dbo].[sp_test_section_item_update_all]
GO

CREATE PROCEDURE [dbo].[sp_test_section_item_update_all]
     @TEST_SECTION_ID numeric
    ,@QUESTION_IDs varchar(MAX)
    
AS
BEGIN

	DELETE [dbo].[TEST_SECTION_ITEM] WHERE [TEST_SECTION_ID] = @TEST_SECTION_ID;
	
	DECLARE @query varchar(MAX);
    SET @query = 'INSERT INTO [dbo].[TEST_SECTION_ITEM]
    ( 
         [TEST_SECTION_ID]
        ,[QUESTION_ID]
    )
    SELECT ' + CAST(@TEST_SECTION_ID AS varchar(18)) + ', [QUESTION_ID]
	FROM [QUESTION]
	WHERE [QUESTION_ID] IN (' + @QUESTION_IDs + ')';

	EXEC (@query);

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_delete')
   DROP PROCEDURE [dbo].[sp_test_delete]
GO

CREATE PROCEDURE [dbo].[sp_test_delete]
     @TEST_ID numeric
    
AS
BEGIN
	BEGIN TRAN;

    DELETE FROM [dbo].[TEST]
    WHERE [TEST_ID] = @TEST_ID;

	DELETE FROM [dbo].[TEST_SECTION_ITEM]
	WHERE [TEST_SECTION_ID] IN 
		(SELECT TEST_SECTION_ID FROM [dbo].[TEST_SECTION] WHERE TEST_ID = @TEST_ID);

	DELETE FROM [dbo].[TEST_SECTION] WHERE TEST_ID = @TEST_ID;

	COMMIT TRAN;
END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_questions')
   DROP PROCEDURE [dbo].[sp_test_section_questions]
GO

CREATE PROCEDURE [dbo].[sp_test_section_questions]
     @TEST_SECTION_ID numeric
    
AS
BEGIN
	SELECT Q.QUESTION_ID, M.ITEM_ID, Q.QUESTION_DESCRIPTION, Q.QUESTION_TYPE, M.ITEM_FACE, L.FIGURE_PATH
	FROM [dbo].[TEST_SECTION_ITEM] I INNER JOIN [dbo].[QUESTION] Q ON I.QUESTION_ID = Q.QUESTION_ID
		LEFT JOIN [dbo].[QUESTION_FIGURE] F ON F.QUESTION_ID = Q.QUESTION_ID
		LEFT JOIN [dbo].[FIGURELINK] L ON F.FIGURE_ID = L.FIGURE_ID
		INNER JOIN [dbo].[QUESTION_ITEM] M ON Q.QUESTION_ID = M.QUESTION_ID
		LEFT JOIN [dbo].[QUESTION_ITEM_FIGURE] R ON M.ITEM_ID = R.ITEM_ID
	WHERE I.TEST_SECTION_ID = @TEST_SECTION_ID;
END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_selete_question_figure')
   DROP PROCEDURE [dbo].[sp_selete_question_figure]
GO

CREATE PROCEDURE [dbo].[sp_selete_question_figure]
     @QUESTION_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_FIGURE]
    WHERE [QUESTION_ID] = @QUESTION_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_select_question_info')
   DROP PROCEDURE [dbo].[sp_select_question_info]
GO

CREATE PROCEDURE [dbo].[sp_select_question_info]
     @QUESTION_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_INFO]
    WHERE [QUESTION_ID] = @QUESTION_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_item_select')
   DROP PROCEDURE [dbo].[sp_question_item_select]
GO

CREATE PROCEDURE [dbo].[sp_question_item_select]
     @QUESTION_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_ITEM] 
    WHERE [QUESTION_ID] = @QUESTION_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_item_figure_select')
   DROP PROCEDURE [dbo].[sp_question_item_figure_select]
GO

CREATE PROCEDURE [dbo].[sp_question_item_figure_select]
     @ITEM_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_ITEM_FIGURE] 
    WHERE [ITEM_ID] = @ITEM_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_solution_select')
   DROP PROCEDURE [dbo].[sp_question_solution_select]
GO

CREATE PROCEDURE [dbo].[sp_question_solution_select]
     @ITEM_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_SOLUTION] 
    WHERE [ITEM_ID] = @ITEM_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_selete_figure_link')
   DROP PROCEDURE [dbo].[sp_selete_figure_link]
GO

CREATE PROCEDURE [dbo].[sp_selete_figure_link]
     @FIGURE_ID numeric

AS
BEGIN

    DELETE FROM [dbo].[FIGURELINK]
    WHERE [FIGURE_ID] = @FIGURE_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_selete_question_figure')
   DROP PROCEDURE [dbo].[sp_selete_question_figure]
GO

CREATE PROCEDURE [dbo].[sp_selete_question_figure]
     @QUESTION_ID numeric  

AS
BEGIN

    SELECT * FROM [dbo].[QUESTION_FIGURE]
    WHERE [QUESTION_ID] = @QUESTION_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_select')
   DROP PROCEDURE [dbo].[sp_question_select]
GO

CREATE PROCEDURE [dbo].[sp_question_select]
     @QUESTION_ID numeric
    
AS
BEGIN

    SELECT * FROM [dbo].[QUESTION] 
    WHERE [QUESTION_ID] = @QUESTION_ID

END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_update_sections')
   DROP PROCEDURE [dbo].[sp_test_update_sections]
GO

CREATE PROCEDURE [dbo].[sp_test_update_sections]
     @TEST_ID numeric
    ,@COURSE_ID char(10)
    ,@USER_ID char(10)
    ,@TEST_DESC ntext
    ,@TEST_CREATION_DATE datetime
    ,@TEST_START_TIME datetime
    ,@TEST_DURATION int
	,@TEST_SECTIONS_ID varchar(MAX)
    
AS
BEGIN
	BEGIN TRAN;

	IF LEN(@TEST_SECTIONS_ID) > 0
	BEGIN
		DECLARE @query varchar(MAX);

		SET @query = 'DELETE FROM [dbo].[TEST_SECTION_ITEM]
		WHERE [TEST_SECTION_ID] IN 
			(SELECT [TEST_SECTION_ID] 
			FROM [dbo].[TEST_SECTION]
			WHERE [TEST_ID] = ' + CAST(@TEST_ID AS varchar(18)) + ' AND [TEST_SECTION_ID] NOT IN (' + @TEST_SECTIONS_ID + '))';

		EXEC (@query);

		SET @query = 'DELETE FROM [dbo].[TEST_SECTION]
		WHERE [TEST_ID] = ' + CAST(@TEST_ID AS varchar(18)) + ' AND [TEST_SECTION_ID] NOT IN (' + @TEST_SECTIONS_ID + ')';

		EXEC (@query);
	END

	UPDATE [dbo].[TEST]
    SET 
         [COURSE_ID] = @COURSE_ID
        ,[USER_ID] = @USER_ID
        ,[TEST_DESC] = @TEST_DESC
        ,[TEST_CREATION_DATE] = @TEST_CREATION_DATE
        ,[TEST_START_TIME] = @TEST_START_TIME
        ,[TEST_DURATION] = @TEST_DURATION
        
    WHERE [TEST_ID] = @TEST_ID;

	COMMIT TRAN;
END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_course_get')
   DROP PROCEDURE [dbo].[sp_course_get]
GO

CREATE PROCEDURE [dbo].[sp_course_get]
	@COURSE_ID char(10)
	   
AS
BEGIN

	SELECT [COURSE_ID], [COURSE_NAME]
	FROM [dbo].[COURSE]
	WHERE LTRIM(RTRIM([COURSE_ID])) = LTRIM(RTRIM(@COURSE_ID));

END
GO
/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_question_solution_insert')
   DROP PROCEDURE [dbo].[sp_question_solution_insert]
GO

CREATE PROCEDURE [dbo].[sp_question_solution_insert]
     @ITEM_ID numeric
    ,@QUESTION_ID numeric
    ,@SOLUTION_ORDER tinyint
    
AS
BEGIN

    INSERT INTO [dbo].[QUESTION_SOLUTION]
    ( 
         [ITEM_ID]
        ,[QUESTION_ID]
        ,[SOLUTION_ORDER]
        
    )
    VALUES
    (
         @ITEM_ID
        ,@QUESTION_ID
        ,@SOLUTION_ORDER
        
    )
END
GO

/*-------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_test_section_question_ids')
   DROP PROCEDURE [dbo].[sp_test_section_question_ids]
GO

CREATE PROCEDURE [dbo].[sp_test_section_question_ids]
     @QUESTIONIDS varchar(MAX)
    
AS
BEGIN
	DECLARE @query varchar(MAX);
	SET @query = '
	SELECT Q.QUESTION_ID, M.ITEM_ID, Q.QUESTION_DESCRIPTION, Q.QUESTION_TYPE, M.ITEM_FACE, L.FIGURE_PATH
	FROM [dbo].[TEST_SECTION_ITEM] I INNER JOIN [dbo].[QUESTION] Q ON I.QUESTION_ID = Q.QUESTION_ID
		LEFT JOIN [dbo].[QUESTION_FIGURE] F ON F.QUESTION_ID = Q.QUESTION_ID
		LEFT JOIN [dbo].[FIGURELINK] L ON F.FIGURE_ID = L.FIGURE_ID
		INNER JOIN [dbo].[QUESTION_ITEM] M ON Q.QUESTION_ID = M.QUESTION_ID
		LEFT JOIN [dbo].[QUESTION_ITEM_FIGURE] R ON M.ITEM_ID = R.ITEM_ID
	WHERE Q.QUESTION_ID IN (' + @QUESTIONIDS + ')';

	EXEC (@query);

END
GO